import * as Koa from 'koa';
const path = require('path');
import getConnection from '../mysql/connection';
import { generateSql } from './sql';
import { dealwithMySQLDate } from '../utils/common';

/**
 * 增加
 * @param {*} user 
 */
export async function addPicture(ctx: Koa.Context, data: any)  {
  let connection: any = null;
  try {
    if (!data) return { success: false };
    const db = ctx.dbPool;
    connection = await getConnection(db);
    const sql = generateSql("picture", "insert", data);
    if (!sql) return { msg: "生成sql语句错误", success: false };
    await connection.execute(sql, Object.values(data));
    return { success: true };
  } catch (error) {
    return { msg: error.sqlMessage || "", success: false };
  } finally {
    if (connection) connection.release();
  }
}

/**
 * 删除
 * @param {*} id 用户ID
 */
export async function delelePicture(ctx: Koa.Context, params: Object) {
  let connection: any = null;
  try {
    const db = ctx.dbPool;
    connection = await getConnection(db);
    const sql = generateSql("picture", "delete", params);
    if (!sql) return { success: false };
    await connection.execute(sql);
    return { success: true };
  } catch (error) {
    return { msg: error.sqlMessage || "", success: false };
  } finally {
    if (connection) connection.release();
  }
}

/**
 * 更新
 * @param {*} user 
 */
export async function updatePicture(ctx: Koa.Context, data: any)  {
  let connection: any = null;
  try {
    if (!data?.pid) return { msg: "更新信息错误，缺少信息ID", success: false };
    const db = ctx.dbPool;
    connection = await getConnection(db);
    const sql = generateSql("picture", "update", data);
    if (!sql) return { success: false };
    await connection.execute(sql, Object.values(data));
    return { success: true };
  } catch (error) {
    return { msg: error.sqlMessage || "", success: false };
  } finally {
    if (connection) connection.release();
  }
}

/**
 * 查询
 * @param {*} params 查询条件
 */
export async function searchPicture(ctx: Koa.Context, params?: any)  {
  let connection: any = null;
  try {
    const db = ctx.dbPool;
    connection = await getConnection(db);
    const sql = generateSql("picture", "query", params);
    if (!sql) return { success: false };
    const [result] = await connection.execute(sql);
    return { success: true, data: dealwithMySQLDate(result, { capture_time: "date", create_time: "datetime", update_time: "datetime" }) };
  } catch (error) {
    return { msg: error.sqlMessage || "", success: false };
  } finally {
    if (connection) connection.release();
  }
}

export async function searchPictureTotalNumbers(ctx: Koa.Context, params?: any)  {
  let connection: any = null;
  try {
    const db = ctx.dbPool;
    connection = await getConnection(db);
    const [result] = await connection.execute("SELECT COUNT(*) FROM picture" + (params.condition ? (" " + params.condition) : ""));
    return { success: true, data: result?.[0]?.["COUNT(*)"] || 0 };
  } catch (error) {
    return { msg: error.sqlMessage || "", success: false };
  } finally {
    if (connection) connection.release();
  }
}
